﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_EmployeeTaxs : IDAL_FIM_EmployeeTaxs
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_FIM_EmployeeTaxs()
        {

        }

        public IList<M_FIM_EmployeeTaxs> GetFull(Nullable<int> emp_type_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_EmployeeTaxs> infoList = new List<M_FIM_EmployeeTaxs>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);

                strSQL = "SELECT tax.emp_tax_seq, tax.emp_seq, tax.fund_type_seq, tax.fund_plus_per"
                    + ", tax.ins_amount, tax.rmf_amount, tax.ltf_amount, tax.loan_amount"

                    + ", emp.emp_type_seq, emp.pos_seq, p.pos_no, p.pos_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", emp.working_job_site_seq, j.job_site_name as working_job_site_name"

                    + ", emp.tax_payer, emp.marr_stat_seq, mar.marr_stat_name, ch1.child_edu_count"
                    + ", ch2.child_non_edu_count, par.parent_count"

                    + ", tax.record_status, tax.created_by, tax.created_when, tax.updated_by, tax.updated_when"
                    + " FROM fim_employee_taxs tax"
                    + " LEFT JOIN hrm_ps_employees emp ON tax.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions p ON emp.pos_seq = p.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " LEFT JOIN mas_married_statuses mar ON emp.marr_stat_seq = mar.marr_stat_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch1 ON emp.emp_seq = ch1.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch2 ON emp.emp_seq = ch2.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as parent_count"
                        + " FROM hrm_ps_emp_parents"
                        + " WHERE care_status = '1'"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") par ON emp.emp_seq = par.emp_seq"
                    + " WHERE tax.record_status not like @delete_status"
                    + (emp_type_seq != null ? " and emp.emp_type_seq = @emp_type_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_EmployeeTaxs info = new M_FIM_EmployeeTaxs();
                        info.emp_tax_seq = Convert.ToInt32(drc[i]["emp_tax_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.fund_type_seq = cn.ConvertNullInt(drc[i]["fund_type_seq"]);
                        info.fund_plus_per = cn.ConvertNullDouble(drc[i]["fund_plus_per"]);
                        info.ins_amount = cn.ConvertNullDouble(drc[i]["ins_amount"]);
                        info.rmf_amount = cn.ConvertNullDouble(drc[i]["rmf_amount"]);
                        info.ltf_amount = cn.ConvertNullDouble(drc[i]["ltf_amount"]);
                        info.loan_amount = cn.ConvertNullDouble(drc[i]["loan_amount"]);

                        info.emp_type_seq = cn.ConvertNullInt(drc[i]["emp_type_seq"]);
                        info.pos_seq = cn.ConvertNullInt(drc[i]["pos_seq"]);
                        info.pos_no = drc[i]["pos_no"].ToString();
                        info.pos_name = drc[i]["pos_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.working_job_site_seq = cn.ConvertNullInt(drc[i]["working_job_site_seq"]);
                        info.working_job_site_name = drc[i]["working_job_site_name"].ToString();

                        info.tax_payer = drc[i]["tax_payer"].ToString();
                        info.marr_stat_seq = cn.ConvertNullInt(drc[i]["marr_stat_seq"]);
                        info.marr_stat_name = drc[i]["marr_stat_name"].ToString();
                        info.child_edu_count = cn.ConvertNullInt(drc[i]["child_edu_count"]);
                        info.child_non_edu_count = cn.ConvertNullInt(drc[i]["child_non_edu_count"]);
                        info.parent_count = cn.ConvertNullInt(drc[i]["parent_count"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_EmployeeTaxs GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_EmployeeTaxs info = new M_FIM_EmployeeTaxs();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT tax.emp_tax_seq, tax.emp_seq, tax.fund_type_seq, tax.fund_plus_per"
                    + ", tax.ins_amount, tax.rmf_amount, tax.ltf_amount, tax.loan_amount"

                    + ", emp.emp_type_seq, emp.pos_seq, p.pos_no, p.pos_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", emp.working_job_site_seq, j.job_site_name as working_job_site_name"

                    + ", emp.tax_payer, emp.marr_stat_seq, mar.marr_stat_name, ch1.child_edu_count"
                    + ", ch2.child_non_edu_count, par.parent_count"

                    + ", tax.record_status, tax.created_by, tax.created_when, tax.updated_by, tax.updated_when"
                    + " FROM fim_employee_taxs tax"
                    + " LEFT JOIN hrm_ps_employees emp ON tax.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions p ON emp.pos_seq = p.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " LEFT JOIN mas_married_statuses mar ON emp.marr_stat_seq = mar.marr_stat_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch1 ON emp.emp_seq = ch1.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch2 ON emp.emp_seq = ch2.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as parent_count"
                        + " FROM hrm_ps_emp_parents"
                        + " WHERE care_status = '1'"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") par ON emp.emp_seq = par.emp_seq"
                    + " WHERE tax.emp_tax_seq not like @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.emp_tax_seq = Convert.ToInt32(drc[0]["emp_tax_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.fund_type_seq = cn.ConvertNullInt(drc[0]["fund_type_seq"]);
                    info.fund_plus_per = cn.ConvertNullDouble(drc[0]["fund_plus_per"]);
                    info.ins_amount = cn.ConvertNullDouble(drc[0]["ins_amount"]);
                    info.rmf_amount = cn.ConvertNullDouble(drc[0]["rmf_amount"]);
                    info.ltf_amount = cn.ConvertNullDouble(drc[0]["ltf_amount"]);
                    info.loan_amount = cn.ConvertNullDouble(drc[0]["loan_amount"]);

                    info.emp_type_seq = cn.ConvertNullInt(drc[0]["emp_type_seq"]);
                    info.pos_seq = cn.ConvertNullInt(drc[0]["pos_seq"]);
                    info.pos_no = drc[0]["pos_no"].ToString();
                    info.pos_name = drc[0]["pos_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.working_job_site_seq = cn.ConvertNullInt(drc[0]["working_job_site_seq"]);
                    info.working_job_site_name = drc[0]["working_job_site_name"].ToString();

                    info.tax_payer = drc[0]["tax_payer"].ToString();
                    info.marr_stat_seq = cn.ConvertNullInt(drc[0]["marr_stat_seq"]);
                    info.marr_stat_name = drc[0]["marr_stat_name"].ToString();
                    info.child_edu_count = cn.ConvertNullInt(drc[0]["child_edu_count"]);
                    info.child_non_edu_count = cn.ConvertNullInt(drc[0]["child_non_edu_count"]);
                    info.parent_count = cn.ConvertNullInt(drc[0]["parent_count"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_EmployeeTaxs GetInfoFromEmpSeq(int emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_EmployeeTaxs info = new M_FIM_EmployeeTaxs();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT tax.emp_tax_seq, tax.emp_seq, tax.fund_type_seq, tax.fund_plus_per"
                    + ", tax.ins_amount, tax.rmf_amount, tax.ltf_amount, tax.loan_amount"

                    + ", emp.emp_type_seq, emp.pos_seq, p.pos_no, p.pos_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", emp.working_job_site_seq, j.job_site_name as working_job_site_name"

                    + ", emp.tax_payer, emp.marr_stat_seq, mar.marr_stat_name, ch1.child_edu_count"
                    + ", ch2.child_non_edu_count, par.parent_count"

                    + ", tax.record_status, tax.created_by, tax.created_when, tax.updated_by, tax.updated_when"
                    + " FROM fim_employee_taxs tax"
                    + " LEFT JOIN hrm_ps_employees emp ON tax.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions p ON emp.pos_seq = p.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON emp.working_job_site_seq = j.job_site_seq"
                    + " LEFT JOIN mas_married_statuses mar ON emp.marr_stat_seq = mar.marr_stat_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch1 ON emp.emp_seq = ch1.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as child_edu_count"
                        + " FROM hrm_ps_emp_childrens"
                        + " WHERE fee_edu = ''"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") ch2 ON emp.emp_seq = ch2.emp_seq"
                    + " LEFT JOIN (SELECT emp_seq, count(*) as parent_count"
                        + " FROM hrm_ps_emp_parents"
                        + " WHERE care_status = '1'"
                        + " AND record_status not like @delete_status"
                        + " GROUP BY emp_seq"
                        + ") par ON emp.emp_seq = par.emp_seq"
                    + " WHERE emp.emp_seq not like @emp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.emp_tax_seq = Convert.ToInt32(drc[0]["emp_tax_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.fund_type_seq = cn.ConvertNullInt(drc[0]["fund_type_seq"]);
                    info.fund_plus_per = cn.ConvertNullDouble(drc[0]["fund_plus_per"]);
                    info.ins_amount = cn.ConvertNullDouble(drc[0]["ins_amount"]);
                    info.rmf_amount = cn.ConvertNullDouble(drc[0]["rmf_amount"]);
                    info.ltf_amount = cn.ConvertNullDouble(drc[0]["ltf_amount"]);
                    info.loan_amount = cn.ConvertNullDouble(drc[0]["loan_amount"]);

                    info.emp_type_seq = cn.ConvertNullInt(drc[0]["emp_type_seq"]);
                    info.pos_seq = cn.ConvertNullInt(drc[0]["pos_seq"]);
                    info.pos_no = drc[0]["pos_no"].ToString();
                    info.pos_name = drc[0]["pos_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.working_job_site_seq = cn.ConvertNullInt(drc[0]["working_job_site_seq"]);
                    info.working_job_site_name = drc[0]["working_job_site_name"].ToString();

                    info.tax_payer = drc[0]["tax_payer"].ToString();
                    info.marr_stat_seq = cn.ConvertNullInt(drc[0]["marr_stat_seq"]);
                    info.marr_stat_name = drc[0]["marr_stat_name"].ToString();
                    info.child_edu_count = cn.ConvertNullInt(drc[0]["child_edu_count"]);
                    info.child_non_edu_count = cn.ConvertNullInt(drc[0]["child_non_edu_count"]);
                    info.parent_count = cn.ConvertNullInt(drc[0]["parent_count"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_FIM_EmployeeTaxs info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@emp_tax_seq", info.emp_tax_seq);
                dbManager.AddParameters(1, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(2, "@fund_type_seq", info.fund_type_seq);
                dbManager.AddParameters(3, "@fund_plus_per", info.fund_plus_per);
                dbManager.AddParameters(4, "@ins_amount", info.ins_amount);
                dbManager.AddParameters(5, "@rmf_amount", info.rmf_amount);
                dbManager.AddParameters(6, "@ltf_amount", info.ltf_amount);
                dbManager.AddParameters(7, "@loan_amount", info.loan_amount);

                dbManager.AddParameters(8, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(9, "@created_by", info.created_by);
                dbManager.AddParameters(10, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_employee_taxs"
                    + "(emp_seq, fund_type_seq, fund_plus_per"
                    + ", ins_amount, rmf_amount, ltf_amount, loan_amount"

                    + ", record_status, created_by, created_when)"
                    + " VALUES(@emp_seq"
                        + ", @fund_type_seq"
                        + ", @fund_plus_per"
                        + ", @ins_amount"
                        + ", @rmf_amount"
                        + ", @ltf_amount"
                        + ", @loan_amount"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_EmployeeTaxs info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@emp_tax_seq", info.emp_tax_seq);
                dbManager.AddParameters(1, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(2, "@fund_type_seq", info.fund_type_seq);
                dbManager.AddParameters(3, "@fund_plus_per", info.fund_plus_per);
                dbManager.AddParameters(4, "@ins_amount", info.ins_amount);
                dbManager.AddParameters(5, "@rmf_amount", info.rmf_amount);
                dbManager.AddParameters(6, "@ltf_amount", info.ltf_amount);
                dbManager.AddParameters(7, "@loan_amount", info.loan_amount);

                dbManager.AddParameters(8, "@updated_by", info.updated_by);
                dbManager.AddParameters(9, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_employee_taxs"
                    + " set emp_seq = @emp_seq"
                    + ", fund_type_seq = @fund_type_seq"
                    + ", fund_plus_per = @fund_plus_per"
                    + ", ins_amount = @ins_amount"
                    + ", rmf_amount = @rmf_amount"
                    + ", ltf_amount = @ltf_amount"
                    + ", loan_amount = @loan_amount"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE emp_tax_seq = @emp_tax_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
